package com.oasystem.dao.jdbc;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Types;
import java.util.List;

import org.springframework.jdbc.core.BatchPreparedStatementSetter;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.PreparedStatementCreator;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;

import com.oasystem.domain.Member;
import com.oasystem.domain.MemberProfitReturnPlan;
import com.oasystem.domain.MemberProfitSearchResult;
import com.oasystem.domain.PagingBean;
import com.oasystem.domain.ProfitDetail;
import com.oasystem.domain.ProfitRecord;
import com.oasystem.domain.ProfitReturnAmountHistory;
import com.oasystem.domain.ProfitReturnedRecord;
import com.oasystem.domain.ProfitSearchResult;
import com.oasystem.domain.ProfitSummary;
import com.oasystem.utils.DateUtils;
import com.oasystem.utils.StringUtils;

public class ProfitDAO {
	private JdbcTemplate jdbcTemplate;

	public void setJdbcTemplate(JdbcTemplate jdbcTemplate) {
		this.jdbcTemplate = jdbcTemplate;
	}

	public void insertInitProfitMemberRecord(long memberID,
			double targetReturnAmount) {
		jdbcTemplate
				.update("insert into profitmemberrecord(memberID,targetReturnAmount,remainAmount) values(?,?,?)",
						new Object[] { memberID, targetReturnAmount,
								targetReturnAmount }, new int[] {
								Types.INTEGER, Types.DOUBLE, Types.DOUBLE });

	}

	public int retrieveShouldReturnProfitMemberCount(String countDate) {
		return jdbcTemplate
				.queryForInt("select count(*) from profitmemberrecord where remainAmount <>0 and createDate < '"
						+ StringUtils.removeSpace(countDate)
						+ " 23:59:59"
						+ "'");
	}

	public void updateTodayShouldReturnAmounts(
			double defaultShouldReturnAmountToday) {
		// 剩余大于今日返利，那么今天返还为今日返利
		String updateDefault = "update profitmemberrecord set todayShouldReturnAmount=? where remainAmount>?";
		// 剩余小于等于今日返利，那么今天返还为今日返利
		String updateSpecial = "update profitmemberrecord set todayShouldReturnAmount=remainAmount where remainAmount<=?";
		jdbcTemplate.update(updateDefault,
				new Object[] { defaultShouldReturnAmountToday,
						defaultShouldReturnAmountToday }, new int[] {
						Types.DOUBLE, Types.DOUBLE });
		jdbcTemplate.update(updateSpecial,
				new Object[] { defaultShouldReturnAmountToday },
				new int[] { Types.DOUBLE });
	}

	public void insertProfitRecord(final ProfitRecord profitRecord,
			String returnDate) {
		String sql = "select count(id) from profitrecord where returnDate like '"
				+ returnDate + "%'";
		int count = jdbcTemplate.queryForInt(sql);
		if (count == 0) {
			KeyHolder key = new GeneratedKeyHolder();
			final String date = StringUtils.removeSpace(returnDate);
			jdbcTemplate.update(new PreparedStatementCreator() {

				@Override
				public PreparedStatement createPreparedStatement(Connection conn)
						throws SQLException {
					PreparedStatement ps = conn
							.prepareStatement("insert into profitrecord(yesterdaySelledAmount,acceptReturnMemberCount,returnPercent,totalReturnAmount,returnDate) values (?,?,?,?,?)");
					ps.setDouble(1, profitRecord.getYesterdaySelledAmount());
					ps.setInt(2, profitRecord.getAcceptReturnMemberCount());
					ps.setDouble(3, profitRecord.getReturnPercent());
					ps.setDouble(4, profitRecord.getTotalReturnAmount());
					ps.setString(5, date);
					return ps;
				}
			}, key);
			profitRecord.setId(key.getKey().intValue());
		} else {
			sql = "select id from profitrecord where returnDate like '"
					+ returnDate + "%'";
			int id = jdbcTemplate.queryForInt(sql);
			profitRecord.setId(id);
			sql = "update profitrecord set yesterdaySelledAmount=?,acceptReturnMemberCount=?,returnPercent=?,totalReturnAmount=? where id=? ";
			jdbcTemplate.update(
					sql,
					new Object[] { profitRecord.getYesterdaySelledAmount(),
							profitRecord.getAcceptReturnMemberCount(),
							profitRecord.getReturnPercent(),
							profitRecord.getTotalReturnAmount(),
							profitRecord.getId() });
		}
	}

	public void retrieveProfitDetails(PagingBean<ProfitDetail> pagingBean,
			String countDate) {
		final int currentRecordIndex = pagingBean.getCurrentRecordIndex();
		final int pageSize = pagingBean.getPageSize();

		String countSql = "select count(m.id) from member m, profitmemberrecord p where m.id=p.memberID and remainAmount>0.0 and  p.createDate<'"
				+ DateUtils.toDateTimeSpace235959(countDate) + "'";
		final String sql = "select * from member m, profitmemberrecord p,operator o where m.id=p.memberID and m.operatorID=o.id and remainAmount>0.0 and p.createDate<'"
				+ DateUtils.toDateTimeSpace235959(countDate) + "' limit ?,?";
		pagingBean.setAllRecordCount(jdbcTemplate.queryForInt(countSql));

		List<ProfitDetail> list = jdbcTemplate.query(
				new PreparedStatementCreator() {

					public PreparedStatement createPreparedStatement(
							Connection con) throws SQLException {
						PreparedStatement ps = con.prepareStatement(sql);
						ps.setInt(1, currentRecordIndex);
						ps.setInt(2, pageSize);
						return ps;
					}
				}, new RowMapper<ProfitDetail>() {

					public ProfitDetail mapRow(ResultSet rs, int rowNum)
							throws SQLException {
						ProfitDetail profitDetail = new ProfitDetail();
						profitDetail.setCreateDate(DateUtils.format(
								"yyyy-MM-dd", rs.getTimestamp("m.createDate")));
						profitDetail.setIdCardNumber(rs
								.getString("idCardNumber"));
						profitDetail.setMemberCardNumber(rs
								.getString("memberCardNumber"));
						profitDetail.setMemberID(rs.getLong("m.id"));
						profitDetail.setMemberName(rs.getString("m.name"));
						profitDetail.setOperatorName(rs.getString("o.name"));
						profitDetail.setPhoneNumber(rs.getString("phoneNumber"));
						profitDetail.setRemainAmount(rs
								.getString("remainAmount"));
						profitDetail.setReturnedAmount(rs
								.getString("totalReturnAmount"));
						profitDetail.setTargetReturnAmount(rs
								.getString("targetReturnAmount"));
						profitDetail.setTodayShouldReturnAmount(rs
								.getString("todayShouldReturnAmount"));
						return profitDetail;
					}
				});
		pagingBean.setData(list);
	}

	public void updateReturnProfitAmount(String memberIDs, double newAmount) {
		memberIDs = memberIDs.replaceAll(" ", "");
		// 剩余大于今日返利，那么今天返还为今日返利
		String defaultSql = "update profitmemberrecord set todayShouldReturnAmount = ? where memberID in ("
				+ memberIDs + ") and remainAmount>?";
		// 剩余小于等于今日返利，那么今天返还为今日返利
		String specialSql = "update profitmemberrecord set todayShouldReturnAmount = remainAmount where memberID in ("
				+ memberIDs + ") and remainAmount<=?";
		jdbcTemplate.update(defaultSql, new Object[] { newAmount, newAmount });
		jdbcTemplate.update(specialSql, new Object[] { newAmount });
	}

	public void batchReturnProfit(String countDate, String memberIDs,
			final String profitID) {
		String[] memberIDArray = memberIDs.split(",");
		if (memberIDArray.length == 0) {
			return;
		}
		// fiilter ids, left ids want to be inserted
		List<String> idList = jdbcTemplate.queryForList(
				"select memberID from memberprofitreturned where returnDate like '"
						+ countDate + "%'", String.class);
		StringBuilder idsForSearch = new StringBuilder();
		int size = idList.size();
		if (size != 0) {
			int len = memberIDArray.length;
			for (int i = 0; i < len; i++) {
				String id = memberIDArray[i];
				if (!idList.contains(id)) {
					idsForSearch.append(id);
					idsForSearch.append(",");
				}
			}
		} else {
			int len = memberIDArray.length;
			for (int i = 0; i < len; i++) {
				String id = memberIDArray[i];
				idsForSearch.append(id);
				idsForSearch.append(",");
			}
		}
		if (idsForSearch.length() == 0) {
			return;
		} else {
			int len = idsForSearch.length();
			if (idsForSearch.lastIndexOf(",") == (len - 1)) {
				idsForSearch.replace(len - 1, len, "");
			}
		}
		String sql = "select memberID,todayShouldReturnAmount from profitmemberrecord where memberID in ("
				+ idsForSearch.toString() + ")";
		final List<MemberProfitReturnPlan> planList = jdbcTemplate.query(sql,
				new RowMapper<MemberProfitReturnPlan>() {

					public MemberProfitReturnPlan mapRow(ResultSet rs,
							int rowNum) throws SQLException {
						MemberProfitReturnPlan m = new MemberProfitReturnPlan();
						m.setMemberID(rs.getLong("memberID"));
						m.setTodayShouldReturnAmount(rs
								.getDouble("todayShouldReturnAmount"));
						return m;
					}

				});
		final int batchSize = planList.size();
		final String returnDate = countDate;
		jdbcTemplate
				.batchUpdate(
						"insert into memberProfitReturned (memberID,todayReturnAmount,profitID,returnDate,createDate) values(?,?,?,?,?)",
						new BatchPreparedStatementSetter() {

							public void setValues(PreparedStatement ps,
									int index) throws SQLException {
								ps.setLong(1, planList.get(index).getMemberID());
								ps.setDouble(2, planList.get(index)
										.getTodayShouldReturnAmount());
								ps.setInt(3, Integer.valueOf(profitID));
								ps.setString(4, returnDate);
								ps.setString(5,
										DateUtils.getTodayDateTimeString());
							}

							public int getBatchSize() {
								return batchSize;
							}
						});

		String updateSql = "update profitmemberrecord set remainAmount=remainAmount-todayShouldReturnAmount,totalReturnAmount=totalReturnAmount+todayShouldReturnAmount where memberID in ("
				+ idsForSearch.toString() + ")";
		jdbcTemplate.update(updateSql);
	}

	public List<ProfitReturnedRecord> retrieveProfitReturnRecordToday() {
		String sql = "select memberCardNumber,idCardNumber,name,phoneNumber,todayReturnAmount from member m, memberprofitreturned p where m.id=p.memberID and p.createDate like '"
				+ DateUtils.getTodayDateString() + "%' order by memberId asc";
		return jdbcTemplate.query(sql, new RowMapper<ProfitReturnedRecord>() {
			public ProfitReturnedRecord mapRow(ResultSet rs, int rowNum)
					throws SQLException {
				ProfitReturnedRecord r = new ProfitReturnedRecord();
				r.setIdCardNumber(rs.getString("idCardNumber"));
				r.setMemberCardNumber(rs.getString("memberCardNumber"));
				r.setName(rs.getString("name"));
				r.setPhoneNumber(rs.getString("phoneNumber"));
				r.setTodayReturnAmount(String.valueOf(rs
						.getDouble("todayReturnAmount")));
				return r;
			}
		});
	}

	public List<ProfitDetail> retrieveProfitDetails() {

		final String sql = "select * from member m, profitmemberrecord p,operator o where m.id=p.memberID and m.operatorID=o.id and remainAmount>0.0";

		return jdbcTemplate.query(sql, new RowMapper<ProfitDetail>() {

			public ProfitDetail mapRow(ResultSet rs, int rowNum)
					throws SQLException {
				ProfitDetail profitDetail = new ProfitDetail();
				profitDetail.setCreateDate(DateUtils.format("yyyy-MM-dd",
						rs.getTimestamp("m.createDate")));
				profitDetail.setIdCardNumber(rs.getString("idCardNumber"));
				profitDetail.setMemberCardNumber(rs
						.getString("memberCardNumber"));
				profitDetail.setMemberID(rs.getLong("m.id"));
				profitDetail.setMemberName(rs.getString("m.name"));
				profitDetail.setOperatorName(rs.getString("o.name"));
				profitDetail.setPhoneNumber(rs.getString("phoneNumber"));
				profitDetail.setRemainAmount(rs.getString("remainAmount"));
				profitDetail.setReturnedAmount(rs
						.getString("totalReturnAmount"));
				profitDetail.setTargetReturnAmount(rs
						.getString("targetReturnAmount"));
				profitDetail.setTodayShouldReturnAmount(rs
						.getString("todayShouldReturnAmount"));
				return profitDetail;
			}
		});
	}

	public ProfitSummary loadProfitSummary(String from, String to) {
		ProfitSummary summary = new ProfitSummary();
		String returnAmount = "select sum(todayReturnAmount) from memberProfitReturned where returnDate between ? and ?";

		Double result = jdbcTemplate.queryForObject(returnAmount, new Object[] {
				from, to }, new int[] { Types.TIMESTAMP, Types.TIMESTAMP },
				Double.class);
		summary.setReturnAmount(StringUtils.doubleToString(
				result.doubleValue(), 2));

		String totalReturnAmount = "select sum(todayReturnAmount) from memberProfitReturned";
		result = jdbcTemplate.queryForObject(totalReturnAmount, Double.class);
		summary.setTotalReturnAmount(StringUtils.doubleToString(
				result.doubleValue(), 2));

		String memberCount = "select count(id) from memberProfitReturned where returnDate between ? and ?";
		summary.setMemberCount(String.valueOf(jdbcTemplate.queryForInt(
				memberCount, new Object[] { from, to }, new int[] {
						Types.TIMESTAMP, Types.TIMESTAMP })));

		String totalMemberCount = "select count(id) from memberProfitReturned";
		summary.setTotalMemberCount(String.valueOf(jdbcTemplate
				.queryForInt(totalMemberCount)));

		String profitAmount = "select sum(yesterdaySelledAmount) from profitrecord where  returnDate between ? and ?";
		result = jdbcTemplate.queryForObject(profitAmount, new Object[] { from,
				to }, new int[] { Types.TIMESTAMP, Types.TIMESTAMP },
				Double.class);
		summary.setProfitAmount(StringUtils.doubleToString(
				result.doubleValue(), 2));

		String totalProfitAmount = "select sum(yesterdaySelledAmount) from profitrecord ";
		result = jdbcTemplate.queryForObject(totalProfitAmount, Double.class);
		summary.setTotalProfitAmount(StringUtils.doubleToString(
				result.doubleValue(), 2));

		return summary;
	}

	public void retrieveProfitDetails(final String from, final String to,
			PagingBean<ProfitSearchResult> pagingBean) {
		final int currentRecordIndex = pagingBean.getCurrentRecordIndex();
		final int pageSize = pagingBean.getPageSize();

		String countSql = "select count(p.id) from profitRecord p where  p.returnDate between ? and ?";
		pagingBean.setAllRecordCount(jdbcTemplate.queryForInt(countSql,
				new Object[] { from, to }));

		final String sql = "select p.returnDate,totalReturnAmount,acceptReturnMemberCount,yesterdaySelledAmount,sum(m.todayReturnAmount) as returned from profitRecord p left join memberprofitreturned m on p.id=m.profitID where p.returnDate between ? and ? group by p.id limit ?,?";
		pagingBean.setData(jdbcTemplate.query(new PreparedStatementCreator() {

			public PreparedStatement createPreparedStatement(Connection con)
					throws SQLException {
				PreparedStatement ps = con.prepareStatement(sql);
				ps.setString(1, from);
				ps.setString(2, to);
				ps.setInt(3, currentRecordIndex);
				ps.setInt(4, pageSize);
				return ps;
			}
		}, new RowMapper<ProfitSearchResult>() {

			public ProfitSearchResult mapRow(ResultSet rs, int rowNum)
					throws SQLException {
				ProfitSearchResult result = new ProfitSearchResult();
				result.setReturnDate(rs.getString("p.returnDate").substring(0,
						10));
				result.setProfitAmount(StringUtils.doubleToString(
						rs.getDouble("yesterdaySelledAmount"), 2));
				result.setMemberCount(rs.getString("acceptReturnMemberCount"));

				result.setReturnedProfitAmount(StringUtils.doubleToString(
						rs.getDouble("returned"), 2));
				result.setTotalReturnAmount(StringUtils.doubleToString(
						rs.getDouble("totalReturnAmount"), 2));
				return result;
			}
		}));

	}

	public void searchForPagingList(Member member, String recordType,
			PagingBean<MemberProfitSearchResult> pagingBean) {
		final int currentRecordIndex = pagingBean.getCurrentRecordIndex();
		final int pageSize = pagingBean.getPageSize();

		StringBuffer wherePhase = new StringBuffer(" where 1=1 ");

		if (!member.getMemberCardNumber().equals("")) {
			wherePhase.append(" and m.memberCardNumber like '%"
					+ member.getMemberCardNumber() + "%'");
		}
		if (!member.getName().equals("")) {
			wherePhase.append(" and m.name like '%" + member.getName() + "%'");
		}
		if (!member.getIdCardNumber().equals("")) {
			wherePhase.append(" and idCardNumber like '%"
					+ member.getIdCardNumber() + "%'");
		}
		if (!member.getPhoneNumber().equals("")) {
			wherePhase.append(" and phoneNumber like '%"
					+ member.getPhoneNumber() + "%'");
		}
		if (!member.getAddress().equals("")) {
			wherePhase.append(" and address like '%" + member.getAddress()
					+ "%'");
		}
		if (member.getOperatorID() != 0 && member.getOperatorID() != -1) {
			wherePhase.append(" and operatorID=" + member.getOperatorID());
		}
		if (member.getGoodsID() != 0 && member.getGoodsID() != -1) {
			wherePhase.append(" and goodsID=" + member.getGoodsID());
		}
		if (member.getDepartmentID() != 0 && member.getDepartmentID() != -1) {
			wherePhase.append(" and departmentID=" + member.getDepartmentID());
		}
		if (member.getGoodsPrice() != null) {
			wherePhase.append(" and m.goodsPrice=" + member.getGoodsPrice());
		}
		if (member.getProfitReturnPercent() != null) {
			wherePhase.append(" and profitReturnPercent="
					+ member.getProfitReturnPercent());
		}
		if (member.getProfitReturnAmount() != null) {
			wherePhase.append(" and profitReturnAmount="
					+ member.getProfitReturnAmount());
		}
		if (!member.getFromDate().equals("") && !member.getToDate().equals("")) {
			wherePhase.append(" and m.createDate between '"
					+ member.getFromDate() + " 00:00:00" + "' and '"
					+ member.getToDate() + " 23:59:59" + "' ");
		}
		if ("Finished".equals(recordType)) {
			wherePhase.append(" and remainAmount=0.0 ");
		} else if ("UnFinished".equals(recordType)) {
			wherePhase.append(" and remainAmount<>0.0 ");
		}
		pagingBean
				.setAllRecordCount(jdbcTemplate
						.queryForInt("select count(*) from member m,profitmemberrecord p "
								+ wherePhase.toString()
								+ " and m.id=p.memberID"));

		// String sql =
		// "select * from member where concat(memberCardNumber,name,idCardNumber,phoneNumber,address,operatorID,goodsID,departmentID,goodsPrice,profitReturnPercent,profitReturnAmount,description,createDate) like '%'?'%'?'%'?'%'?'%'?'%'?'%'?'%'?'%'?'%'?'%'?'%'?'%'?'%'";
		final String sql = "select * from member m,operator o,goods g,department d,profitmemberrecord p "
				+ wherePhase.toString()
				+ " and m.operatorID=o.id and m.goodsID=g.id and m.departmentID=d.id and m.id=p.memberID limit ?,?";
		pagingBean.setData(jdbcTemplate.query(new PreparedStatementCreator() {

			public PreparedStatement createPreparedStatement(Connection con)
					throws SQLException {
				PreparedStatement ps = con.prepareStatement(sql);
				ps.setInt(1, currentRecordIndex);
				ps.setInt(2, pageSize);
				return ps;
			}
		}, new RowMapper<MemberProfitSearchResult>() {

			public MemberProfitSearchResult mapRow(ResultSet rs, int rowNum)
					throws SQLException {
				MemberProfitSearchResult member = new MemberProfitSearchResult();
				member.setId(rs.getLong("m.id"));
				member.setAddress(rs.getString("m.address"));
				member.setCreateDateString(DateUtils.format("yyyy-MM-dd",
						rs.getTimestamp("m.createDate")));
				member.setDepartmentID(rs.getInt("m.departmentID"));
				member.setDepartmentName(rs.getString("d.departmentName"));
				member.setDescription(rs.getString("m.description"));
				member.setGoodsID(rs.getInt("m.goodsID"));
				member.setGoodsName(rs.getString("g.name"));
				member.setGoodsPrice(rs.getInt("m.goodsPrice"));
				member.setIdCardNumber(rs.getString("m.idCardNumber"));
				member.setMemberCardNumber(rs.getString("m.memberCardNumber"));
				member.setName(rs.getString("m.name"));
				member.setOperatorID(rs.getInt("m.operatorID"));
				member.setOperatorName(rs.getString("o.name"));
				member.setPhoneNumber(rs.getString("m.phoneNumber"));
				member.setProfitReturnAmount(rs
						.getDouble("m.profitReturnAmount"));
				member.setProfitReturnPercent(rs
						.getDouble("m.profitReturnPercent"));

				member.setTodayReturnAmount(String.valueOf(rs
						.getString("todayShouldReturnAmount")));
				member.setReturnedAmount(String.valueOf(rs
						.getDouble("totalReturnAmount")));
				member.setRemainAmount(String.valueOf(rs
						.getDouble("remainAmount")));
				member.setTargetProfitAmount(String.valueOf(rs
						.getDouble("targetReturnAmount")));
				return member;
			}
		}));

	}

	public double retrieveTotalSelledAmount(String from, String to) {
		String sql = "select sum(goodsPrice) from member where createDate between ? and ?";
		Double sum = jdbcTemplate.queryForObject(sql,
				new Object[] { from, to }, Double.class);
		return sum == null ? 0.00D : sum.doubleValue();
	}

	public List<ProfitReturnAmountHistory> getReturnHistory(String memberID) {
		String sql = "select * from memberprofitreturned where memberID=?";
		return jdbcTemplate.query(sql, new Object[] { memberID },
				new RowMapper<ProfitReturnAmountHistory>() {
					@Override
					public ProfitReturnAmountHistory mapRow(ResultSet rs,
							int arg1) throws SQLException {
						ProfitReturnAmountHistory h = new ProfitReturnAmountHistory();
						h.setReturnDate(rs.getString("returnDate"));
						h.setReturnAmount(rs.getString("todayReturnAmount"));
						return h;
					}
				});
	}

	public boolean isCountDateExist(String countDate) {

		String sql = "select count(*) from profitmemberrecord where returnDate like '"
				+ StringUtils.removeSpace(countDate) + "%'";
		int count = jdbcTemplate.queryForInt(sql);
		return count != 0 ? true : false;
	}

	public List<String> getAlreadyReturnedMembersByCountDate(String countDate) {
		List<String> idList = jdbcTemplate.queryForList(
				"select memberID from memberprofitreturned where returnDate like '"
						+ countDate + "%'", String.class);
		return idList;
	}

	public void returnProfitForMembers(String countDate,
			List<String> exclusiveMembers, final String profitID) {

		StringBuilder sb = new StringBuilder(" ");
		if (!exclusiveMembers.isEmpty()) {
			sb.append(" memberID not in ( ");
			int size = exclusiveMembers.size();
			for (int i = 0; i < size; i++) {
				sb.append(exclusiveMembers.get(i));
				if (i < size - 1) {
					sb.append(",");
				}
			}
			sb.append(") and ");
		}

		String sql = "select memberID,todayShouldReturnAmount from profitmemberrecord where "
				+ sb.toString()
				+ " remainAmount <>0 and createDate < '"
				+ StringUtils.removeSpace(countDate) + " 23:59:59'";
		final List<MemberProfitReturnPlan> planList = jdbcTemplate.query(sql,
				new RowMapper<MemberProfitReturnPlan>() {

					public MemberProfitReturnPlan mapRow(ResultSet rs,
							int rowNum) throws SQLException {
						MemberProfitReturnPlan m = new MemberProfitReturnPlan();
						m.setMemberID(rs.getLong("memberID"));
						m.setTodayShouldReturnAmount(rs
								.getDouble("todayShouldReturnAmount"));
						return m;
					}

				});
		final int batchSize = planList.size();
		final String returnDate = countDate;
		jdbcTemplate
				.batchUpdate(
						"insert into memberProfitReturned (memberID,todayReturnAmount,profitID,returnDate,createDate) values(?,?,?,?,?)",
						new BatchPreparedStatementSetter() {

							public void setValues(PreparedStatement ps,
									int index) throws SQLException {
								ps.setLong(1, planList.get(index).getMemberID());
								ps.setDouble(2, planList.get(index)
										.getTodayShouldReturnAmount());
								ps.setInt(3, Integer.valueOf(profitID));
								ps.setString(4, returnDate);
								ps.setString(5,
										DateUtils.getTodayDateTimeString());
							}

							public int getBatchSize() {
								return batchSize;
							}
						});

		if (batchSize != 0) {
			StringBuilder updatedMembers = new StringBuilder();
			int i = 0, size = batchSize;
			for (MemberProfitReturnPlan m : planList) {
				updatedMembers.append(m.getMemberID());
				i = i + 1;
				if (i < size) {
					updatedMembers.append(",");
				}
			}
			String updateSql = "update profitmemberrecord set remainAmount=remainAmount-todayShouldReturnAmount,totalReturnAmount=totalReturnAmount+todayShouldReturnAmount where memberID in ("
					+ updatedMembers.toString() + ")";
			jdbcTemplate.update(updateSql);
		}

	}

}
